﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data;
using KPIS.GERP.GHRMS.DAL;
using KPIS.GERP.GHRMS.MODEL;
using KPIS.GERP.GHRMS.IBLL;
using KPIS.DBM;

namespace KPIS.GERP.GHRMS.BLL
{
    public class 
        DisciplinComplainBLL
    {
        DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

        public DataSet Select()
        {
            
            try
            {
                string strSQL;

                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@DeleteRecord", SystemInfo._DeleteRecordStatus);
                
                strSQL = " SELECT c.*"
                    + ", ct.CMP_TYPE_NAME"
                    + ", CASE WHEN c.CMP_EMP_SEQ IS NULL THEN c.CMP_NAME"
                        + " ELSE CONCAT(np.PREFIX_NAME, e.FIRST_NAME, '  ', e.LAST_NAME)"
                        + " END AS CMP_TEXT"
                    + ", CONCAT(np2.PREFIX_NAME, e2.FIRST_NAME, '  ', e2.LAST_NAME) AS REC_NAME"
                    + " FROM HRM_DC_COMPLAINS c"
                    + " LEFT JOIN HRM_DC_COMPLAIN_TYPES ct ON c.CMP_TYPE_SEQ = ct.CMP_TYPE_SEQ"
                    + " LEFT JOIN HRM_PS_EMPLOYEES e ON c.CMP_EMP_SEQ = e.EMP_SEQ"
                    + " LEFT JOIN MAS_NAME_PREFIXES np ON e.NAME_PREFIX_SEQ = np.NAME_PREFIX_SEQ"
                    + " LEFT JOIN HRM_PS_EMPLOYEES e2 ON c.REC_EMP_SEQ = e2.EMP_SEQ"
                    + " LEFT JOIN MAS_NAME_PREFIXES np2 ON e2.NAME_PREFIX_SEQ = np2.NAME_PREFIX_SEQ"
                    + " WHERE c.RECORD_STATUS not like @DeleteRecord";

                return dbManager.ExecuteDataSet(CommandType.Text, strSQL);
            }

            catch (Exception ex)
            {
                return null;
            }

            finally
            {
                dbManager.Dispose();
            }

        }
        public DataSet ListComplainTypes()
        {

            try
            {
                string strSQL;

                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@RecordStatus", SystemInfo._ActiveRecordStatus);

                strSQL = " SELECT *"
                    + " FROM HRM_DC_COMPLAIN_TYPES"
                    + " WHERE RECORD_STATUS = @RecordStatus";
                return dbManager.ExecuteDataSet(CommandType.Text, strSQL);
            }

            catch (Exception ex)
            {
                return null;
            }

            finally
            {
                dbManager.Dispose();
            }

        }
        public void Insert(DateTime CmpDate, int CmpTypeSeq, Nullable<int> CmpEmpSeq, string CmpName
            , string CmpDescr, int ReceiveSeq, string Remarks, int CreatedBy)
        {
            try
            {
                string strSQL;

                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(10);

                dbManager.AddParameters(0, "@cmp_date", CmpDate);
                dbManager.AddParameters(1, "@cmp_type_seq", CmpTypeSeq);
                dbManager.AddParameters(2, "@cmp_emp_seq", CmpEmpSeq);
                dbManager.AddParameters(3, "@cmp_name", CmpName);
                dbManager.AddParameters(4, "@cmd_descr", CmpDescr);
                dbManager.AddParameters(5, "@rec_emp_seq", ReceiveSeq);
                dbManager.AddParameters(6, "@remarks", Remarks);
                dbManager.AddParameters(7, "@record_status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(8, "@created_by", CreatedBy);
                dbManager.AddParameters(9, "@created_when", DateTime.Today);

                strSQL = " INSERT INTO HRM_DC_COMPLAINS"
                    + "(CMP_DATE, CMP_TYPE_SEQ, CMP_EMP_SEQ, CMP_NAME, CMP_DESCR, REC_EMP_SEQ"
                        + ", REMARKS, RECORD_STATUS, CREATED_BY, CREATED_WHEN)"
                    + " VALUES(@cmp_date, @cmp_type_seq, @cmp_emp_seq, @cmp_name, @cmd_descr"
                        + ", @rec_emp_seq, @remarks, @record_status, @created_by, @created_when)";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();

            }

            catch (Exception ex)
            {
                dbManager.RollBackTransaction();
                throw new Exception(ex.Message);
            }

            finally
            {
                dbManager.Dispose();
            }

        }
        public void Update(int Sequence, DateTime CmpDate, int CmpTypeSeq, Nullable<int> CmpEmpSeq, string CmpName
            , string CmpDescr, int ReceiveSeq, string Remarks, int UpdatedBy)
        {
            try
            {
                string strSQL;

                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(10);

                dbManager.AddParameters(0, "@cmp_date", CmpDate);
                dbManager.AddParameters(1, "@cmp_type_seq", CmpTypeSeq);
                dbManager.AddParameters(2, "@cmp_emp_seq", CmpEmpSeq);
                dbManager.AddParameters(3, "@cmp_name", CmpName);
                dbManager.AddParameters(4, "@cmd_descr", CmpDescr);
                dbManager.AddParameters(5, "@rec_emp_seq", ReceiveSeq);
                dbManager.AddParameters(6, "@remarks", Remarks);
                dbManager.AddParameters(7, "@cmp_seq", Sequence);
                dbManager.AddParameters(8, "@updated_by", UpdatedBy);
                dbManager.AddParameters(9, "@updated_when", DateTime.Today);

                strSQL = " UPDATE HRM_DC_COMPLAINS"
                    + " SET CMP_DATE = @cmp_date"
                        + ", CMP_TYPE_SEQ = @cmp_type_seq"
                        + ", CMP_EMP_SEQ = @cmp_emp_seq"
                        + ", CMP_NAME = @cmp_name"
                        + ", CMP_DESCR = @cmd_descr"
                        + ", REC_EMP_SEQ = @rec_emp_seq"
                        + ", REMARKS = @remarks"
                        + ", UPDATED_BY = @updated_by"
                        + ", UPDATED_WHEN = @updated_when"
                    + " WHERE CMP_SEQ = @cmp_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }

            catch (Exception ex)
            {
                dbManager.RollBackTransaction();
                throw new Exception(ex.Message);
            }

            finally
            {
                dbManager.Dispose();
            }

        }
        public void Delete(int Sequence, int UpdatedBy)
        {
            try
            {
                string strSQL;

                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@cmp_seq", Sequence);
                dbManager.AddParameters(1, "@record_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(2, "@updated_by", UpdatedBy);
                dbManager.AddParameters(3, "@updated_when", DateTime.Today);

                strSQL = " UPDATE HRM_DC_COMPLAINS"
                    + " SET RECORD_STATUS = @record_status"
                        + ", UPDATED_BY = @updated_by"
                        + ", UPDATED_WHEN = @updated_when"
                    + " WHERE CMP_SEQ = @cmp_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }

            catch (Exception ex)
            {
                dbManager.RollBackTransaction();
                throw new Exception(ex.Message);
            }

            finally
            {
                dbManager.Dispose();
            }

        }
    }
}
